1. Field of the Invention
This invention relates to enterprise information management (xe2x80x9cEIMxe2x80x9d), and, more particularly, to enterprise information management systems and associated methods. The term xe2x80x9centerprisexe2x80x9d refers to the corporate headquarters, the management company, and the individual stores.
2. Background
Consolidations in the automotive retail channel have brought about new financial complexities for the management companies. Acquiring dealerships which operate on a variety of dealer management systems, has made it difficult for the acquiring enterprise to get a consolidated view of the financial position of and of inventories throughout the enterprise. Additionally, a trend has appeared in the automotive industry to become publicly held in order to raise capital for further expansion. Financial statements report the results of performance and the financial position of an automotive retail enterprise.
The task of consolidation is currently a time and labor intensive task. It has been determined that customers want to maximize the profitability of the enterprise by identifing key drivers of business success and leveraging the knowledge throughout the enterprise.
Providing the ability for customizable, consolidated financial, operating and inventory reporting from multiple sources will allow the enterprise and store managers to achieve increased bottom-line gains by out performing existing manual data extraction and manipulation process. Additionally, the easier, timely reporting provides better mechanisms for identifying operation inefficiencies, savings or opportunities.
The consolidation process starts with extracting data from the operational systems. The data is then combined to provide an overall view of the conditions and performance of the enterprise. The process of combining the data requires that a common chart of accounts is developed for financials, accounts are summarized and adjustments are made. For other data, mapping occurs to get a consistent view of the data Adjustments are made for inter-company transactions, foreign currency conversions, deferred taxes, goodwill, etc.
Financial consolidation encompasses three primary statements: balance sheet, income statement, and statement of cash flows. The balance sheet is a statement of the resources of the enterprise (assets) and the claims on those assets (liabilities and owners"" equity) at some point in time. The income statement provides information about the operating performance of an enterprise for a particular period of time. The cash flow statement reports the amount of cash flow from an enterprisers operating, investing, and financing activities. It also shows the primary sources and uses of cash from these activities.
In one aspect, this invention, EIM, provides a commercial data warehouse product. EIM is a novel combination of vendor purchased software and customized software development. EIM includes data extraction and movement, data transformation and cleansing, database update and tuning, and database access.
Thus, in one aspect, this invention is a system for enterprise information management (xe2x80x9cEIMxe2x80x9d). The system includes a data warehouse server and a transformation and staging server connected to the data warehouse server for providing transformed and cleansed data to the data warehouse server. The EIM system also includes a data source application connected to the transformation and staging server to provide data to the transformation and staging server, wherein the transformation and staging server obtains data from the data source application via requests and places the data into temporary staging tables to prepare for the transformation and cleansing process prior to movement of the data to the data warehouse server. A financial consolidation application is connected to the transformation and staging server for performing consolidation and reporting of financial data. A web server is connected to the data warehouse server. A plurality of clients are connectable to the web server for accessing data from the data warehouse server via the web server.
In some preferred embodiments, the transformation and staging server itself comprises four physical disk drives and wherein the physical disk drives are separated by individual logical drives, wherein the disk drives serve separate purposes and functionality of the system has been separated by drive so as to minimize disk contention during the various process that run on the transformation and staging server. In some preferred embodiments, the transformation and staging server includes the following databases and files: a master database that contains all system tables; a model database template for the creation of all new databases; a staging database which contains all data from systems that is being or has been cleansed, transformed, denormalized, summarized; an error database which contains source information that could not automatically be cleansed or transformed; a financial database used by the financial consolidation application and containing consolidated and summarized financial information; at least one flat file for loads; a repository database used by the transformation tool and containing information regarding the transformation procedures and automation plans; and a temporary work area for the creation of temporary tables and information. In some of the preferred embodiments of the present invention, the data warehouse server includes the following databases and files: a master database that contains all of the system tables on the data warehouse server; a model database template for the creation of all new databases on the data warehouse server; a temporary database used as a temporary work area for the creation of temporary tables and information on the data warehouse server; a meta-view database that contains all of the metadata, views, and stored procedures that are to be used by the users of the system; and a database that contains some normalized data, but mostly denormalized and summarized data that has been cleansed, transformed on the transformation and staging server, wherein the meta-view database is the sole access method for users to the information in the database.
In some embodiments of the present invention, the system for EIM has a data warehouse server; a transformation and staging server connected to the data warehouse server for providing transformed and cleansed data to the data warehouse server; a data source application connected to the transformation and staging server to provide data to the transformation and staging server; a financial consolidation application connected to the transformation and staging server for performing consolidation and reporting of financial data; a web server connected to the data warehouse server; and a plurality of clients connectable to the web server for accessing data from the data warehouse server via the web server.
Sometimes the transformation and staging server obtains data from the data source application via requests and places the data into temporary staging tables to prepare for the transformation and cleansing process prior to movement of the data to the data warehouse server.
The transformation and staging server may include the following databases and files: a master database that contains all system tables; a model database template for the creation of all new databases; a staging database which contains all data from systems that is being or has been cleansed, transformed, denormalized, summarized; an error database which contains source information that could not automatically be cleansed or transformed; a financial database used by the financial consolidation application and containing consolidated and summarized financial information; at least one flat file for loads; a repository database used by the transformation tool and containing information regarding the transformation procedures and automation plans; and a temporary work area for the creation of temporary tables and information.
The transformation and staging server may have four physical disk drives and wherein the physical disk drives for the transformation and staging server, are separated by individual logical drives.
In some preferred embodiments, the disk drives serve separate purposes and functionality of the system has been separated by drive so as to minimize disk contention during the various process that run on the transformation and staging server.
The data warehouse server may include the following databases and files: a master database that contains all of the system tables on the data warehouse server; a model database template for the creation of all new databases on the data warehouse server; a temporary database used as a temporary work area for the creation of temporary tables and information on the data warehouse server; a meta-view database that contains all of the metadata, views, and stored procedures that are to be used by the users of the system; and a database that contains some normalized data, but mostly denormalized and summarized data that has been cleansed, transformed on the transformation and staging server.
In some preferred embodiments, the meta-view database is the sole access method for users to the information in the database.
The data warehouse server may comprise a plurality of physical disk drives combined into one logical drive to take full advantage of Redundant Array of Independent Disks (RAID) technology.
In preferred embodiments of this invention, the data relate to automobiles.
In some preferred embodiments of this invention, a client accessing the data warehouse server via the web server is presented with a login screen and is required to provide a valid user identifier and password in order to access the system, and wherein, upon authentication of the user identifier and password, a new browser window is opened at the client and the client is presented with an interface tailored to the client""s preferences and security privileges.
The user interface (UI) may comprise a task list, buttons/controls for launching components of the system; and a content area for task related data and output, wherein each item listed in the task list has supporting screens that populate the content area.
The UI may provide access to performance indicators through home pages made up of one to four reports or graphs displayed on the content area.
In preferred embodiments of this invention, home pages can be customized.
Clients may be provided with a set of predefined drill paths or next steps for the user to follow in analyzing their data, wherein the predefined next steps display additional reports with new, more detailed, or related information to a report currently being viewed. The predefined next step buttons may be only provided with the original reports provided with the system.
In another aspect, this invention is a method, in a system for enterprise information management comprising a data warehouse server; a transformation and staging server connected to the data warehouse server for providing transformed and cleansed data to the data warehouse server; a data source application connected to the transformation and staging server to provide data to the transformation and staging server; a financial consolidation application connected to the transformation and staging server for performing consolidation and reporting of financial data; a web server connected to the data warehouse server; and a plurality of clients connectable to the web server for accessing data from the data warehouse server via the web server. In some preferred embodiments, the method includes, by the transformation and staging server: retrieving operational data from the data source application using a data flow plan; validating and cleansing the retrieved operational data; loading the data is loaded into an appropriate temporary staging table; and performing a second phase of validations and cleansing, including referential integrity validations and aggregate processing validations.
The method may also include writing data with an error to an error table along with an error message describing a reason for rejection. The method of some embodiments also includes correcting errors and reprocessing the data.
In some preferred embodiments, the validations and transformations performed include at least one of: checking for absence of data; checking data typing; checking free form text fields; checking multipurpose fields; checking dummy values entered in a mandatory field; checking for contradicting data; checking violation of business rules; checking data anomalies; restructuring operational keys; checking data integrity; supplying default values; renaming data elements moved from the operational environment to the data warehouse; data format conversion; and aggregating data values.
The method may also include loading information from the transformation and staging server on the data Warehouse server.
Sometimes the loading of information comprises one of: a round-robin approach used for refresh processing and extracting information from permanent tables; and a see-saw approach used for non-refresh processing and extracting information from temporary tables.
In some embodiments, the round-robin approach is used if the system requires minimal down time.
Preferably the round-robin approach comprises: (a) exporting information to flat files on the transformation and staging server, selecting rows from normalized, denormalized, and summary tables; (b) loading information into the normalized, denormalized, and summary load tables; (c) for each table: (c1) updating load statistics metadata on the data warehouse server, indicating that the information is in a xe2x80x9cloadingxe2x80x9d state; (c2) renaming the current table to a temporary table as a temporary holding area; (c3) renaming the load table to the actual table, making the latest information available to the users; (c4) renaming the temporary table to the load table to prepare for the next period""s processing batch; (c5) updating the load statistics metadata on the data warehouse server indicating that the information is in a xe2x80x9cupdatedxe2x80x9d state and updating the date, time, number of records loaded; (d) deleting all of the information from the normalized, denormalized, and summary load tables to prepare for the next period""s processing batch.
Preferably the see-saw approach comprises: (a) exporting information to flat files on the transformation and staging server, selecting rows from the normalized, denormalized, and summary temporary tables; (b) for each table of the normalized, denormalized, and summary temporary tables: (b1) updating the load statistics metadata on the data warehouse server indicating that the information is in a xe2x80x9cloadingxe2x80x9d state; (b2) renaming the current table load table; (b3) loading information into the load table; (b4) renaming the load table to the permanent table; (b5) updating the load statistics metadata on the data warehouse server indicating that the information is in a xe2x80x9cupdatedxe2x80x9d state and updating the date, time, number of records loaded.
The method may include storing historic information on the transformation and staging server. Sometimes the method includes storing historical information on both the data warehouse server and the transformation and staging server, and using the non-refresh processing approach on the transformation and staging server and the see-saw approach on the data warehouse server.
In another aspect, this invention is a computerized record-keeping system that tracks and records transactions of a method in a system for enterprise information management comprising a data warehouse server; a transformation and staging server connected to the data warehouse server for providing transformed and cleansed data to the data warehouse server; a data source application connected to the transformation and staging server to provide data to the transformation and staging server; a financial consolidation application connected to the transformation and staging server for performing consolidation and reporting of financial data; a web server connected to the data warehouse server; and a plurality of clients connectable to the web server for accessing data from the data warehouse server via the web server. The method includes, by the transformation and staging server: retrieving operational data from the data source application using a data flow plan; validating and cleansing the retrieved operational data; loading the data is loaded into an appropriate temporary staging table; and performing a second phase of validations and cleansing, including referential integrity validations and aggregate processing validations.
In some embodiments, the method of the system may include writing data with an error to an error table along with an error message describing a reason for rejection. In some embodiments, the method of the system may include correcting errors and reprocessing the data.
The validations and transformations performed may include at least one of: checking for absence of data; checking data typing; checking free form text fields; checking multipurpose fields; checking dummy values entered in a mandatory field; checking for contradicting data; checking violation of business rules; checking data anomalies; restructuring operational keys; checking data integrity; supplying default values; renaming data elements moved from the operational environment to the data warehouse; data format conversion; and aggregating data values.
The method may include loading information from the transformation and staging server on the data warehouse server. The loading of information may include a round-robin approach used for refresh processing and extracting information from permanent tables; and a see-saw approach used for non-refresh processing and extracting information from temporary tables.